/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import pojo.TuyenBayPOJO;

/**
 *
 * @author b2
 */
public class TuyenBayDAO {

    public static ArrayList<TuyenBayPOJO> LayDanhSachTuyenBay() {
        ArrayList<TuyenBayPOJO> ds = new ArrayList<TuyenBayPOJO>();
        try {
            String sql = "SELECT * FROM TUYENBAY";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                TuyenBayPOJO tb = new TuyenBayPOJO();
                tb.setMaTuyenBay(rs.getString("MaTuyenBay"));
                tb.setSanBayDen(rs.getString("SanBayDen"));
                tb.setSanBayDi(rs.getString("SanBayDi"));
                ds.add(tb);
            }
            helper.close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    public static TuyenBayPOJO LayTuyenBay(String maTuyenBay) {

        TuyenBayPOJO tb = new TuyenBayPOJO();
        try {
            String sql = "SELECT * FROM TUYENBAY WHERE MaTuyenBay = '" + maTuyenBay + "'";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                tb.setMaTuyenBay(rs.getString("MaTuyenBay"));
                tb.setSanBayDen(rs.getString("SanBayDen"));
                tb.setSanBayDi(rs.getString("SanBayDi"));
            }
            helper.close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return tb;
    }
    //lấy sân bay trong tuyến bay

    public static ArrayList<TuyenBayPOJO> LayTuyenBayDi(String sanbayDen) {
        ArrayList<TuyenBayPOJO> ds = new ArrayList<TuyenBayPOJO>();
        try {
            String sql = "SELECT * FROM TUYENBAY WHERE SANBAYDEN = '" + sanbayDen + "'";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                TuyenBayPOJO tb = new TuyenBayPOJO();
                tb.setMaTuyenBay(rs.getString("MaTuyenBay"));
                tb.setSanBayDen(rs.getString("SanBayDen"));
                tb.setSanBayDi(rs.getString("SanBayDi"));
                ds.add(tb);
            }
            helper.close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    public static ArrayList<TuyenBayPOJO> LayTuyenBayDen(String sanbayDi) {
        ArrayList<TuyenBayPOJO> ds = new ArrayList<TuyenBayPOJO>();
        try {
            String sql = "SELECT * FROM TUYENBAY WHERE SANBAYDi = '" + sanbayDi + "'";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                TuyenBayPOJO tb = new TuyenBayPOJO();
                tb.setMaTuyenBay(rs.getString("MaTuyenBay"));
                tb.setSanBayDen(rs.getString("SanBayDen"));
                tb.setSanBayDi(rs.getString("SanBayDi"));
                ds.add(tb);
            }
            helper.close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    public static boolean themTuyenBay(TuyenBayPOJO tb) {
        boolean kq = false;
        String sql = String.format("INSERT INTO `TUYENBAY` (`MaTuyenBay` ,`SanBayDi`,`SanBayDen`)VALUES ('%s', '%s' , '%s');", tb.getMaTuyenBay(), tb.getSanBayDi(), tb.getSanBayDen());
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        int n = helper.executeUpdate(sql);
        if (n == 1) {
            kq = true;
        }
        helper.close();

        return kq;
    }

    public static ArrayList<TuyenBayPOJO> TimKiemTyenBayTheoTen(String matuyenbay) {
        ArrayList<TuyenBayPOJO> ds = new ArrayList<TuyenBayPOJO>();
        try {
            String sql = "SELECT * FROM TUYENBAY WHERE MATUYENBAY LIKE '%" + matuyenbay + "%'";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                TuyenBayPOJO tb = new TuyenBayPOJO();
                tb.setMaTuyenBay(rs.getString("MaTuyenBay"));
                tb.setSanBayDen(rs.getString("SanBayDen"));
                tb.setSanBayDi(rs.getString("SanBayDi"));
                //
                ds.add(tb);
            }
            helper.close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }
    // phương thức xóa phòng ban

    public static boolean xoaTuyenBay(String maTuyenBay) {
        boolean kq = false;
        String sql = "DELETE FROM TUYENBAY WHERE MATUYENBAY = '" + maTuyenBay + "'";
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        int n = helper.executeUpdate(sql);
        if (n == 1) {
            kq = true;
        }
        helper.close();
        return kq;
    }
}
